﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using Utils;
using Bll;
using System.IO;
using Entity.ERP;
using MongoMigration;
using Entity.BBC;

namespace Analysis.Controllers
{
    public class UseApiController : ApiController
    {
        public static Dictionary<string, AddressModel> addressTemp = new Dictionary<string, AddressModel>();

        [HttpGet]
        public Result<bool> Test()
        {
            Result<bool> result = new Result<bool>();
            result.Body = false;
            result.Msg = "fuck";
            result.Status = 500;

            string fuck= new Migrator().select("select Name,Address,Tel from dbo.cdsgus where Name like '%c' and Tel='1' or Id>=1000");


            //new Migrator().bracketPrettifier("Name like '%c' and Tel='1' or Id>=1000");

            //where Name='cheng' or Tel='1'                            //db.dbo.cdsgus.find($or[{Name:"cheng"},{Tel:"1"}])
            //where Name='cheng' and Tel='1';                          //db.dbo.cdsgus.find({Name:"cheng",Tel:"1"})  db.dbo.cdsgus.find({$and:[{Name:"cheng"},{Tel:"1"}]})
            //where Name='cheng' or Tel='1' and Id>1000                //db.dbo.cdsgus.find({$or:[{Name:"cheng"},{Tel:"1"}],Id:{$lt:1000}})
            //where Name in（"nihao","fuck"） and Tel='1' or id<1000   //db.dbo.cdsgus.find({$or:[{Name:{$in:["nihao","fuck"]},Tel='1'},{id:{$lt:1000}}]})
            //where Name in ("nihao","fuck") or Tel='1' or id>1000     //db.dbo.cdsgus.find({$or:[{Name:{$in:["nihao","fuck"]}},{Tel:"1"},{id:{$gt:1000}}]})
            //where Name like "%c%";                                   //db.dbo.cdsgus.find({Name:/.c./})
            //where Name like "c%"                                     //db.dbo.cdsgus.find({Name:/^c/})
            //where Name like "%c";                                    //db.dbo.cdsgus.find({Name:/.c$/})
            //where Name like '%c' and Tel='1' or Id>=1000             //db.dbo.cdsgus.find({$or:[{Name:/.c$/,Tel:"1"},{id:{$gt:1000}}]})
            
            




            
            



            return result;
        }

        #region sqlServer 数据库创建实体
        [HttpGet]
        public Result<bool> CreateSql2Model(string typeName="BBC")
        {
            Result<bool> result = new Result<bool>();
            result.Body = true;
            result.Msg = "成功";
            result.Status = 200;

            string modelPath = "d://Sql2Model/" + typeName;


            if (Directory.Exists(modelPath))
            {
                Directory.Delete(modelPath,true);
            }
            Directory.CreateDirectory(modelPath);

            List<string> tableNameList = new List<string>();
            string sql = "select name from sysobjects where xtype='U'";
            if (typeName == "BBC")
            {
                tableNameList = (List<string>)BllBBCCommon.SelectSingleValueBySql("sysobjects", sql, "string", true);
            }
            else if (typeName=="SYJ")
            {
                tableNameList = (List<string>)BllSYJCommon.SelectSingleValueBySql("sysobjects", sql, "string", true);
            }


            foreach (string tableName in tableNameList)
            {
                string className = ChangeTableName(tableName);
                string fileName = modelPath + "/" + className + ".cs";
                //if (File.Exists(fileName))
                //{
                //    File.Delete(fileName);
                //}

                FileStream fs = File.Create(fileName);

                string xxx = "";
                string packageName = "";

                if (typeName == "BBC")
                {
                    packageName = "Entity.BBC";
                    string sqlBBCTable = "exec createEntity @ObjectName='" + tableName + "',@ClassName='" + className + "',@PackageName='"+packageName+"'";
                    var temp = BllBBCCommon.SelectSingleValueBySql("", sqlBBCTable);
                    xxx = temp==null?"":temp.ToString();
                }
                else if (typeName == "SYJ")
                {
                    packageName = "Entity.SYJ";
                    string sqlSYJTable = "exec createEntity @ObjectName='" + tableName + "',@ClassName='" + className + "',@PackageName='" + packageName + "'";
                    var temp=BllSYJCommon.SelectSingleValueBySql("", sqlSYJTable);
                    xxx = temp==null?"":temp.ToString();
                }

                byte[] data = System.Text.Encoding.Default.GetBytes(xxx);
                //开始写入
                fs.Write(data, 0, data.Length);
                //清空缓冲区、关闭流
                fs.Flush();
                fs.Close();
            }

            return result;

        }

        public string ChangeTableName(string originName)
        {
            string name="";

           var temp= originName.Replace("t_", "").Split('_');

           foreach (string str in temp)
           {
               string newStr = str.Substring(0, 1).ToUpper() + str.Substring(1);
               name += newStr;
           }

            return name;
        }
        #endregion

        #region 根据ip获取省市区

        /// <summary>
        /// 更新浏览记录的真实地址
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public Result<bool> UpdateBrowseRecordAddress()
        {
            Result<bool> result = new Result<bool>();
            bool flag = false;
            string sql = "select id,ip from BrowseRecord where ip!='0.0.0.0' and ProvinceId is null";
            List<SelectBrowseRecord> list= BllBBCCommon.SelectEntityBySql<SelectBrowseRecord>("BrowseRecord", sql);
            if (list != null && list.Count > 0)
            {
                string sqlForUpdate = "";
               List<SProvince> provinceList= BllBBCCommon.SelectEntityBySql<SProvince>("s_province","select * from s_province");
               List<SCity> cityList = BllBBCCommon.SelectEntityBySql<SCity>("s_city", "select * from s_city");
               List<SDistrict> districtList = BllBBCCommon.SelectEntityBySql<SDistrict>("s_district", "select * from s_district");
               int i = 0;
               int z = 0;
                foreach(SelectBrowseRecord record in list)
                {
                    string ip=record.ip;
                    long id=record.id;
                    AddressModel model = new AddressModel();
                    if (addressTemp.Keys.Contains(ip))
                    {
                        model = addressTemp[ip];
                    }
                    else
                    {
                        model = GetAddressByIp(ip);
                        addressTemp.Add(ip, model);
                    }
                   
                    if (model == null)
                    {
                        continue;
                    }
                    string provinceName = model.ProvinceName;
                    string cityName = model.CityName;
                    string regionName = model.RegionName;

                    long provinceId = 0;
                    long cityId = 0;
                    long regionId = 0;
                     SProvince provinceTemp=   provinceList.Where(x => x.ProvinceName.StartsWith(provinceName)).FirstOrDefault();
                     if (provinceTemp != null && provinceTemp.ProvinceID > 0)
                     {
                         provinceId = provinceTemp.ProvinceID;
                         SCity cityTemp = cityList.Where(x => x.CityName.StartsWith(cityName) && x.ProvinceID == provinceId).FirstOrDefault();
                         if (cityTemp != null && cityTemp.CityID > 0)
                         {
                             cityId = cityTemp.CityID;
                             SDistrict districtTemp = districtList.Where(x => x.DistrictName.StartsWith(regionName) && x.CityID==cityId).FirstOrDefault();
                             if (districtTemp != null && districtTemp.DistrictID > 0)
                             {
                                 regionId = districtTemp.DistrictID;
                             }
                         }
                     }
                     if (string.IsNullOrEmpty(sqlForUpdate))
                     {
                         sqlForUpdate = "update BrowseRecord set provinceName='" + provinceName + "',cityName='" + cityName + "',regionName='" + regionName + "',provinceId='" + provinceId + "',cityId='" + cityId + "',regionId='" + regionId + "' where id=" + id + ";\r\n";
                     }
                     else
                     {
                         sqlForUpdate += "update BrowseRecord set provinceName='" + provinceName + "',cityName='" + cityName + "',regionName='" + regionName + "',provinceId='" + provinceId + "',cityId='" + cityId + "',regionId='" + regionId + "' where id=" + id + ";\r\n";
                     }
                     i++;
                     z++;

                     //每达到1000个更新记录则批量更新一次
                     if (i == 1000 || (z == list.Count&&!string.IsNullOrEmpty(sqlForUpdate)))
                     {
                         int j = BllBBCCommon.UpdateEntityBySql("BrowseRecord", sqlForUpdate);

                         if (j == -1)
                         {
                             flag = true;
                             break;
                         }
                         else
                         {
                             i = 0;
                             sqlForUpdate = "";
                         }
                     }
                } 
            }

            if (flag)
            {
                result.Status = 500;
                result.Body = false;
                result.Msg = "更新地址发生错误";
            }
            else
            {
                result.Status = 200;
                result.Body = true;
            }

            return result;
        }

        public class SelectBrowseRecord
        {
            public long id { get; set; }

            public string ip { get; set; }
        }

        /// <summary>
        /// 根据ip地址获取真实用户地址
        /// </summary>
        /// <param name="ip"></param>
        /// <returns></returns>
        public AddressModel GetAddressByIp(string ip)
        {
            string url = "http://whois.pconline.com.cn/ipJson.jsp?ip="+ip+"&level=3";
            string response= WebUtils.DoGet(url,null);
            LogHelper.WriteInfoLog("通过ip获取地址返回："+response);
            response= response.Replace("if(window.IPCallBack) {IPCallBack(", "").Replace(");}","");
            Ip2AddressResult resultForUrl=  SerializeTool.DeSerialize<Ip2AddressResult>(response);
            if (string.IsNullOrEmpty(resultForUrl.err))
            {
                AddressModel model = new AddressModel();
                model.ProvinceName = resultForUrl.pro;
                model.CityName = resultForUrl.city;
                model.RegionName = resultForUrl.region;

                return model;
            }
            else
            {
                return null;
            }
        }

        public class AddressModel
        {
            public string ProvinceName{get;set;}

            public string CityName{get;set;}

            public string RegionName{get;set;}
        }

        public class Ip2AddressResult
        {
            public string ip { get; set; }

            public string pro { get; set; }

            public string proCode { get; set; }

            public string city { get; set; }

            public string cityCode { get; set; }

            public string region { get; set; }

            public string regionCode { get; set; }

            public string addr { get; set; }

            public string regionNames { get; set; }

            public string err { get; set; }
        }

        #endregion
    }
}
